package com.ldj.struts.action;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WriteException;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.actions.DispatchAction;

import com.ldj.common.SystemConfig;
import com.ldj.common.SystemSession;
import com.ldj.common.Upload;
import com.ldj.hibernate.DataDictionary;
import com.ldj.hibernate.DataDictionaryDAO;
import com.ldj.hibernate.EmployerContractInfo;
import com.ldj.hibernate.EmployerContractInfoDAO;
import com.ldj.hibernate.EmployerInfo;
import com.ldj.hibernate.EmployerInfoDAO;
import com.ldj.hibernate.FunctionMenuDAO;
import com.ldj.hibernate.UserAreaScope;
import com.ldj.struts.form.EmployerContractInfoForm;

public class EmployerContractInfoExcelAction extends DispatchAction {

	public ActionForward list(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) {
		EmployerContractInfoForm eForm = (EmployerContractInfoForm)form;
		FunctionMenuDAO fdao = new FunctionMenuDAO(request,response);
		String scorp = "";
		List ulist = fdao.findByUserId(SystemSession.getSupUserId(request));
		if(ulist != null && ulist.size() > 0){
			UserAreaScope uas = (UserAreaScope)ulist.get(0);
			scorp = uas.getUserAreaScopeAreaId();
			if(scorp!=null && !"".equals(scorp)){
				scorp = scorp.replace(",", "','");
			}
			else{
				scorp="";
			}
		}
		EmployerContractInfoDAO dao = new EmployerContractInfoDAO(request, response);
		List list = dao.findMyPage(scorp, eForm);
		request.setAttribute("list", list);
		/*EmployerInfoDAO edao = new EmployerInfoDAO(request, response);
		request.setAttribute("eInfo", edao.findById(request.getParameter("eid")));*/
		return mapping.findForward("list");
	}
	
	
	//用工合同导入
	
	public ActionForward uploadExcel(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) {
		String eid = request.getParameter("eid");
		try {
			HashMap map = Upload.upload(request, "employerContractInfo", ".xls");
			if(map.containsKey("fileName")){
				return new ActionForward("/EmployerContractExcel.do?method=insertExcel&path=" + (String)map.get("fileName"), true);
			}else{
				return new ActionForward("/EmployerContractExcel.do?method=insertExcel", true);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public ActionForward insertExcel(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response){
		List list = new Vector();
		Workbook book =null;
		boolean t = true;
		DataDictionaryDAO ddDAO = new DataDictionaryDAO(request,response);
		EmployerContractInfoDAO dao = new EmployerContractInfoDAO(request,response);
		EmployerInfoDAO eDAO = new EmployerInfoDAO(request,response);
		//将所有的码表信息放入Hashtable中
		Hashtable ht=new Hashtable();
		List<DataDictionary> dList = ddDAO.findAllCode("003","010");
		for(int i=0;i<dList.size();i++){
			DataDictionary dd = dList.get(i);
			ht.put(dd.getDataDictionaryName(), dd.getDataDictionaryCode());
		}
		//将所有的企业名称放入Hashtable中
		Hashtable ht1=new Hashtable();
		List<EmployerInfo> eList = (List<EmployerInfo>)eDAO.findAll();
		for(int i=0;i<eList.size();i++){
			EmployerInfo ei = eList.get(i);
			ht1.put(ei.getEmpInfoName(), ei);
		}
		String name=request.getParameter("path");
		if(name == null || "".equals(name)){
			request.setAttribute("error1", "文件上传失败，请选择正确的文件格式上传！");
			return mapping.findForward("error");
		}else{
			name = request.getRealPath("/employerContractInfo/" + name);
		}
		File file = new File(name);
    	if(!file.exists()){
    		request.setAttribute("error1", "文件上传失败，请选择正确的文件格式上传！");
			return mapping.findForward("error");
    	}
    	int i = 1;
    	try {
			//打开文件
			book = Workbook.getWorkbook(new File(name));
			//获得第一个表的工作对象，“0”表示第一个表
			Sheet sheet = book.getSheet(0);
			int num = sheet.getRows();
			//得到第一列，第一行的单元格（0，0）
			//ArrayList al = new ArrayList();
			for(; i < num; i ++){
				Cell cell0 = sheet.getCell(0,i);
				Cell cell1 = sheet.getCell(1,i);
				Cell cell2 = sheet.getCell(2,i);
				Cell cell3 = sheet.getCell(3,i);
				Cell cell4 = sheet.getCell(4,i);
				Cell cell5 = sheet.getCell(5,i);
				Cell cell6 = sheet.getCell(6,i);
				Cell cell7 = sheet.getCell(7,i);
				Cell cell8 = sheet.getCell(8,i);
				Cell cell9 = sheet.getCell(9,i);
				Cell cell10 = sheet.getCell(10,i);
				Cell cell11 = sheet.getCell(11,i);
				Cell cell12 = sheet.getCell(12,i);
				Cell cell13 = sheet.getCell(13,i);
				Cell cell14 = sheet.getCell(14,i);
				Cell cell15 = sheet.getCell(15,i);
				Cell cell16 = sheet.getCell(16,i);
				Cell cell17 = sheet.getCell(17,i);
				
				String empInfoName = cell0.getContents();
				String empConEmpName = cell1.getContents();
				String empConEmpSex = cell2.getContents();
				String empConIdCardNum = cell3.getContents();
				String empConEducationLevel =cell4.getContents();
				String empConHouse =cell5.getContents();
				String empConAddress =cell6.getContents();
				String empConPhone =cell7.getContents();
				String empConWordType =cell8.getContents();
				String empConWorkType =cell9.getContents();
				String empConCompetence =cell10.getContents();
				String empConRecontractendTime =cell11.getContents();
				String empConEndCouse =cell12.getContents();
				String empConPost =cell13.getContents();
				String empConContractEndTime =cell14.getContents();
				String empConContractBeginTime =cell15.getContents();
				String empConContractTime =cell16.getContents();
				String empConEmpDate =cell17.getContents();
				
				EmployerContractInfo info = new EmployerContractInfo();
				info.setEmpConEmpName(empConEmpName);
				info.setEmpConIdCardNum(empConIdCardNum);
				info.setEmpConHouse(empConHouse);
				info.setEmpConAddress(empConAddress);
				info.setEmpConPhone(empConPhone);
				info.setEmpConWordType(empConWordType);
				info.setEmpConCompetence(empConCompetence);
				info.setEmpConPost(empConPost);
				info.setEmpConContractType("0");
				info.setEmpConDelOper("0");
				info.setEmpConState("0");
				
				if(empInfoName !=null && !"".equals(empInfoName)){
					if(ht1.containsKey(empInfoName))
					{
						EmployerInfo eInfo = (EmployerInfo)ht1.get(empInfoName);
						info.setEmployerInfo(eInfo);
					}
					else{
							request.setAttribute("error", "第"+(i+1)+"行，企业名称列企业名称不存在!");
							t = false;
							break;
					}
				}
				else{
					request.setAttribute("error", "第"+(i+1)+"行，企业名称列企业名称不能为空!");
					t = false;
					break;
				}
				if(empConEmpName ==null || "".equals(empConEmpName)){
					request.setAttribute("error", "第"+(i+1)+"行，职工姓名不能为空!");
					t = false;
					break;
				}
				
				if(empConEmpSex !=null && !"".equals(empConEmpSex)){
					if( !"男".equals(empConEmpSex) && !"女".equals(empConEmpSex)){
						request.setAttribute("error", "第"+(i+1)+"行，性别列只能输入男或女!");
						t = false;
						break;
					}
				}
				
				if(empConEmpSex !=null && "男".equals(empConEmpSex)){
					info.setEmpConEmpSex("0");
				}
				
				if(empConEmpSex !=null && "女".equals(empConEmpSex)){
					info.setEmpConEmpSex("1");
				}
				
					if(empConCompetence !=null && !"".equals(empConCompetence)){
						if( !"无".equals(empConCompetence) && !"初级".equals(empConCompetence) && !"中级".equals(empConCompetence) && !"高级".equals(empConCompetence)){
							request.setAttribute("error", "第"+(i+1)+"行，职业资格证书列只能为无、初级、中级、高级中的一种!");
							t = false;
							break;
						}
					}
				
				if(empConCompetence !=null && "无".equals(empConCompetence)){
					info.setEmpConCompetence("0");
				}
				if(empConCompetence !=null && "初级".equals(empConCompetence)){
					info.setEmpConCompetence("1");
				}
				if(empConCompetence !=null && "中级".equals(empConCompetence)){
					info.setEmpConCompetence("2");
				}
				if(empConCompetence !=null && "高级".equals(empConCompetence)){
					info.setEmpConCompetence("3");
				}
				
				if(empConEducationLevel !=null && !"".equals(empConEducationLevel)){
					if(ht.containsKey(empConEducationLevel)){
					String educationLevelCode = (String)ht.get(empConEducationLevel);
					info.setEmpConEducationLevel(educationLevelCode);
					}else{
							request.setAttribute("error", "第"+(i+1)+"行，输入的文化程度不存在!");
							t = false;
							break;
						}
					}
				
				if(empConWorkType !=null && !"".equals(empConWorkType)){
					if(ht.containsKey(empConWorkType)){
						String workCode = (String)ht.get(empConWorkType);
						info.setEmpConWorkType(workCode);
					}else{
						request.setAttribute("error", "第"+(i+1)+"行，输入的用工形式不存在!");
						t = false;
						break;
					}
				}
						
				if(empConContractTime !=null && !"".equals(empConContractTime)){
					if(!"1年以下".equals(empConContractTime) && !"1-3年".equals(empConContractTime) && !"3-5年".equals(empConContractTime) && !"无固定期限".equals(empConContractTime)){
						request.setAttribute("error", "第"+(i+1)+"行，输入的合同期限只能为1年以下、1-3年、3-5年、无固定期限中的一种!");
						t = false;
						break;
					}
				}
				
				if(empConContractTime !=null && "1年以下".equals(empConContractTime)){
					info.setEmpConContractTime("0");
				}
				if(empConContractTime !=null && "1-3年".equals(empConContractTime)){
					info.setEmpConContractTime("1");
				}
				if(empConContractTime !=null && "3-5年".equals(empConContractTime)){
					info.setEmpConContractTime("2");
				}
				if(empConContractTime !=null && "无固定期限".equals(empConContractTime)){
					info.setEmpConContractTime("3");
				}
				
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				
				if(empConEmpDate !=null && !"".equals(empConEmpDate)){
					 Date empConEmpDate1 =sdf.parse(empConEmpDate);
					 info.setEmpConEmpDate(empConEmpDate1);
				}
				if(empConRecontractendTime !=null && !"".equals(empConRecontractendTime)){
					 Date  empConContractEndTime1= sdf.parse(empConRecontractendTime);
					 info.setEmpConContractEndTime(empConContractEndTime1);
				}
		        
				if(empConContractEndTime !=null && !"".equals(empConContractEndTime)){
					 Date endTime = sdf.parse(empConContractEndTime);
					 info.setEmpConContractEndTime(endTime);
				}
		       
				if(empConContractBeginTime !=null && !"".equals(empConContractBeginTime)){
					 Date beginTime = sdf.parse(empConContractBeginTime);
					 info.setEmpConContractBeginTime(beginTime);
				}
				list.add(info);
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			request.setAttribute("error", "读取导入文件时发生错误!");
			e.printStackTrace();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			request.setAttribute("error", "读取导入文件时发生错误!");
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			request.setAttribute("error", "第"+(i+1)+"行日期数据读取时发生错误，可能时日期格式不对!");
			e.printStackTrace();
		}finally{
			if(book !=null)
				 book.close();
			}
		if(t){
			for(int j = 0; j < list.size(); j ++){
				dao.save((EmployerContractInfo)list.get(j));
			}
		}
    	return mapping.findForward("error");
	}
}